package com.foreknow.elm.dao.impl;

import com.foreknow.elm.dao.CartDao;
import com.foreknow.elm.po.Business;
import com.foreknow.elm.po.Cart;
import com.foreknow.elm.po.Food;
import com.foreknow.elm.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CartDaoImpl implements CartDao {
    private Connection conn;
    private PreparedStatement pst;
    private ResultSet rs;
    @Override
    //1.根据用户编号查询此用户所有购物车信息
    //2.根据用户编号和商家编号，查询此用户购物车中某个商家的所有购物车信息
    public List<Cart> query(Cart cart) throws SQLException {
        List<Cart> list = new ArrayList<>();
        try {
             conn = DBUtil.getConnection();
            String userId =  cart.getUserId();
            Integer businessId = cart.getBusinessId();
            //参数可选,拼串
                StringBuffer sql = new StringBuffer("select * from cart c,business b,food f where c.businessId=b.businessId and c.foodId=f.foodId and userId=?");
               if (businessId!=null){
                    sql.append(" and c.businessId = "+cart.getBusinessId());
                }
               pst = conn.prepareStatement(sql.toString());
               pst.setString(1,userId);
            rs = pst.executeQuery();
            while (rs.next()){
                Cart car = new Cart();
                //userid是字符串
                car.setCarId(Integer.parseInt(rs.getString("cartId")));
                car.setFoodID(Integer.parseInt(rs.getString("foodId")));
                car.setBusinessId(Integer.parseInt(rs.getString("businessId")));
                car.setUserId(rs.getString("userId"));
                car.setQuantity(Integer.parseInt(rs.getString("quantity")));
                Business business = new Business();
                business.setBusinessId(Integer.parseInt(rs.getString("b.businessId")));
                business.setBusinessName(rs.getString("b.businessName"));
                business.setBusinessAddress(rs.getString("b.businessAddress"));
                business.setBusinessExplain(rs.getString("b.businessExplain"));
                business.setBusinessImg(rs.getString("b.businessImg"));
                business.setOrderTypeId(Integer.parseInt(rs.getString("b.orderTypeId")));
                business.setStarPrice(Double.valueOf(rs.getString("b.starPrice")));
                business.setDeliveryPrice(Double.valueOf(rs.getString("b.deliveryPrice")));
                business.setRemarks(rs.getString("b.remarks"));
                car.setBusiness(business);
                Food food = new Food();
                food.setFoodId(rs.getInt("f.foodId"));
                food.setFoodName(rs.getString("f.foodName"));
                food.setFoodExplain(rs.getString("f.foodExplain"));
                food.setFoodImg(rs.getString("f.foodImg"));
                food.setFoodPrice(rs.getDouble("f.foodPrice"));
                food.setBusinessId(rs.getInt("f.businessId"));
                food.setRemarks(rs.getString("f.remarks"));
                car.setFood(food);
                list.add(car);
            }
        }finally {
            DBUtil.close(rs,pst);
        }
        return list;
    }
// 向购物车表中添加一条记录
    @Override
    public int insert(Cart cart) throws SQLException {
        int a = 0;
        String sql = "insert into cart (userId,businessId,foodId,quantity) values (?,?,?,1)";
        try {
            conn = DBUtil.getConnection();
            pst = conn.prepareStatement(sql);
                String uid = cart.getUserId();
                Integer bid = cart.getBusinessId();
                Integer fid = cart.getFoodID();
            pst.setString(1,uid);
            pst.setInt(2,bid);
            pst.setInt(3,fid);
            a = pst.executeUpdate();
        }finally {
            DBUtil.close(pst);
        }
        return a;
    }
//根据用户编号、商家编号、食品编号更新数量 对象
    @Override
    public int updatequa(Cart cart) throws SQLException {
        int a = 0;
        String sql = "update cart set quantity = ? where userId=? and businessId=? and foodId=?";
        try {
            conn = DBUtil.getConnection();
            pst = conn.prepareStatement(sql);
            Integer qut = cart.getQuantity();
            String uid = cart.getUserId();
            Integer bid = cart.getBusinessId();
            Integer fid = cart.getFoodID();
            pst.setInt(1,qut);
            pst.setString(2,uid);
            pst.setInt(3,bid);
            pst.setInt(4,fid);
            a = pst.executeUpdate();
        }finally {
            DBUtil.close(pst);
        }
        return a;
    }
//根据用户编号、商家编号、食品编号删除购物车表中的一条食品记录
//根据用户编号、商家编号删除购物车表中的多条条记录
    @Override
    public int delete(Cart cart) throws SQLException {
        int a = 0;
        try {
            conn = DBUtil.getConnection();
            StringBuffer sql = new StringBuffer("delete from cart where userId=? and businessId=? ");
            Integer foodId = cart.getFoodID();
            String uid = cart.getUserId();
            Integer bid = cart.getBusinessId();
            if (foodId!=null){
                sql.append("and foodId = ?");
            }
            String sq = sql.toString();
            pst = conn.prepareStatement(sq);
            pst.setString(1,uid);
            pst.setInt(2,bid);
            if (foodId!=null){
                pst.setInt(3,foodId);
            }
            a = pst.executeUpdate();
        }finally {
            DBUtil.close(pst);
        }
        return a;
    }
//根据商家id查询商品数量
    @Override
    public int num(Integer businessId) throws SQLException {
        int a = 0;
        String sql = "select SUM(quantity) from cart where businessId = ?";
        try {
            conn = DBUtil.getConnection();
            pst = conn.prepareStatement(sql);
            pst.setInt(1,businessId);
            rs = pst.executeQuery();
            while (rs.next()){
               a = rs.getInt(1);
            }
        }finally {
            DBUtil.close(rs,pst);
        }
        return a;
    }
}
